﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace BenhVien
{
    public partial class PhieuHenAll : Form
    {
        private DataTable dt1 = new DataTable();
        private DataTable dt2 = new DataTable();
        private DataTable dt3 = new DataTable();
        private DataTable dt4 = new DataTable();
        private DataTable dt5 = new DataTable();
        private DataTable dt6 = new DataTable();
        private SqlDataAdapter da = new SqlDataAdapter();
        Connect conn = new Connect();
        public PhieuHenAll()
        {
            InitializeComponent();
        }

        private void PhieuHenAll_Load(object sender, EventArgs e)
        {
            conn.connect();
            getdata1();
            binding1();
            getdata2();
            binding2();
            getdata3();
            binding3();
           
        }
        private void getdata1() 
        {
            SqlCommand command = new SqlCommand();
            command.Connection = conn.con;
            command.CommandType = CommandType.Text;
            command.CommandText = "Select MaPhieuHen as 'Mã Phiếu Hẹn', MaNV as 'Mã Nhân Viên', TenBN as 'Tên Bệnh Nhân', ThoiGian as 'Thời Gian', PhongKiemTra as 'Phòng Kiểm Tra' from PhieuHen";
            da.SelectCommand = command;
            da.Fill(dt1); 
            dataGridView1.DataSource = dt1;
        }
        private void getdata2()
        {
            SqlCommand command = new SqlCommand();
            command.Connection = conn.con;
            command.CommandType = CommandType.Text;
            command.CommandText = "Select * from PhieuHen";
            da.SelectCommand = command;
            da.Fill(dt2);
            dataGridView2.DataSource = dt2;
        }
        private void getdata3()
        {
            SqlCommand command = new SqlCommand();
            command.Connection = conn.con;
            command.CommandType = CommandType.Text;
            command.CommandText = "Select * from PhieuHen";
            da.SelectCommand = command;
            da.Fill(dt3);
            dataGridView3.DataSource = dt3;
        }
        private void getdata4()
        {
            SqlCommand command = new SqlCommand();
            command.Connection = conn.con;
            command.CommandType = CommandType.Text;
            command.CommandText = "Select * from PhieuHen";
            da.SelectCommand = command;
            da.Fill(dt4);
            dataGridView4.DataSource = dt4;
        }
        private void binding1() 
        {
            textBox1.DataBindings.Clear();
            textBox1.DataBindings.Add("Text", dataGridView1.DataSource, "Mã Phiếu Hẹn");
            textBox2.DataBindings.Clear();
            textBox2.DataBindings.Add("Text", dataGridView1.DataSource, "Tên Bệnh Nhân");
            textBox3.DataBindings.Clear();
            textBox3.DataBindings.Add("Text", dataGridView1.DataSource, "Mã Nhân Viên");
            textBox4.DataBindings.Clear();
            textBox4.DataBindings.Add("Text", dataGridView1.DataSource, "Thời Gian");
            textBox5.DataBindings.Clear();
            textBox5.DataBindings.Add("Text", dataGridView1.DataSource, "Phòng Kiểm Tra");
        }
        private void binding2()
        {
            textBox6.DataBindings.Clear();
            textBox6.DataBindings.Add("Text", dataGridView2.DataSource, "MaPhieuHen");
            textBox7.DataBindings.Clear();
            textBox7.DataBindings.Add("Text", dataGridView2.DataSource, "MaNV");
            textBox8.DataBindings.Clear();
            textBox8.DataBindings.Add("Text", dataGridView2.DataSource, "TenBN");
            textBox9.DataBindings.Clear();
            textBox9.DataBindings.Add("Text", dataGridView2.DataSource, "ThoiGian");
            textBox10.DataBindings.Clear();
            textBox10.DataBindings.Add("Text", dataGridView2.DataSource, "PhongKiemTra");
        }
        private void binding3()
        {
            textBox11.DataBindings.Clear();
            textBox11.DataBindings.Add("Text", dataGridView3.DataSource, "MaPhieuHen");
            textBox12.DataBindings.Clear();
            textBox12.DataBindings.Add("Text", dataGridView3.DataSource, "MaNV");
            textBox13.DataBindings.Clear();
            textBox13.DataBindings.Add("Text", dataGridView3.DataSource, "TenBN");
            textBox14.DataBindings.Clear();
            textBox14.DataBindings.Add("Text", dataGridView3.DataSource, "ThoiGian");
            textBox15.DataBindings.Clear();
            textBox15.DataBindings.Add("Text", dataGridView3.DataSource, "PhongKiemTra");
        }
        //private void binding4()
        //{
        //    textBox16.DataBindings.Clear();
        //    textBox16.DataBindings.Add("Text", dataGridView4.DataSource, "MaPhieuHen");
        //    textBox17.DataBindings.Clear();
        //    textBox17.DataBindings.Add("Text", dataGridView4.DataSource, "MaNV");
        //    textBox18.DataBindings.Clear();
        //    textBox18.DataBindings.Add("Text", dataGridView4.DataSource, "TenBN");
        //    textBox19.DataBindings.Clear();
        //    textBox19.DataBindings.Add("Text", dataGridView4.DataSource, "ThoiGian");
        //    textBox20.DataBindings.Clear();
        //    textBox20.DataBindings.Add("Text", dataGridView4.DataSource, "PhongKiemTra");
        //}

        private bool check(String s,String s1)
        { 
            bool check1=false;
            DataTable data= new DataTable();
            SqlCommand command=new SqlCommand();
            command.Connection=conn.con;
            command.CommandType = CommandType.Text;
            command.CommandText = s;
            command.Parameters.Add("@DoiSo", SqlDbType.NVarChar, 50).Value = s1;
            da.SelectCommand = command;
            da.Fill(data);
            if (data.Rows.Count > 0) check1 = true;
            return check1;
        }

        private bool check1(String s, String s1,String s2)
        {
            bool check1 = false;
            DataTable data = new DataTable();
            SqlCommand command = new SqlCommand();
            command.Connection = conn.con;
            command.CommandType = CommandType.Text;
            command.CommandText = s;
            command.Parameters.Add("@DoiSo", SqlDbType.NVarChar, 50).Value = s1;
            command.Parameters.Add("@DoiSo1", SqlDbType.NVarChar, 50).Value = s2;
            da.SelectCommand = command;
            da.Fill(data);
            if (data.Rows.Count > 0) check1 = true;
            return check1;
        }
        private void button2_Click(object sender, EventArgs e)
        {
            dt1.Clear();
            textBox1.Text = "";
            textBox2.Text = "";
            textBox3.Text = "";
            textBox4.Text = "";
            textBox5.Text = "";
        }

        private void button3_Click(object sender, EventArgs e)
        {
            
        }

        private void button5_Click(object sender, EventArgs e)
        {
           
        }

        private void button7_Click(object sender, EventArgs e)
        {
            
        }

        private void button4_Click(object sender, EventArgs e)
        {
            
          
                String s = textBox6.Text;
                DataRow row = dt2.Select("MaPhieuHen like '" + s + "'")[0];
                row.BeginEdit();
                row["MaNV"] = textBox7.Text;
                row["TenBN"] = textBox8.Text;
                row["ThoiGian"] = textBox9.Text;
                row["PhongKiemTra"] = textBox10.Text;
                row.EndEdit();
                SqlCommand commandUpdate = new SqlCommand();
                commandUpdate.Connection = conn.con;
                commandUpdate.CommandType = CommandType.Text;
                commandUpdate.CommandText = @"Update PhieuHen Set TenBN= @TenBN,
                                                              MaNV= @MaNV,
                                                              ThoiGian= @ThoiGian,
                                                              PhongKiemTra= @PhongKiemTra
                                                              where MaPhieuHen= @MaPhieuHen";
                commandUpdate.Parameters.Add("@MaPhieuHen", SqlDbType.NVarChar, 50, "MaPhieuHen");
                commandUpdate.Parameters.Add("@TenBN", SqlDbType.NVarChar, 50, "TenBN");
                commandUpdate.Parameters.Add("@MaNV", SqlDbType.NVarChar, 50, "MaNV");
                commandUpdate.Parameters.Add("@ThoiGian", SqlDbType.Date, 20, "ThoiGian");
                commandUpdate.Parameters.Add("@PhongKiemTra", SqlDbType.NVarChar, 50, "PhongKiemTra");
                da.UpdateCommand = commandUpdate;
                da.Update(dt2);
                MessageBox.Show("ban da chinh sua thanh cong", "chuc mung", MessageBoxButtons.OK);
    
        }

        private void button6_Click(object sender, EventArgs e)
        {
            String s = textBox11.Text;
            DataRow row = dt3.Select("MaPhieuHen like '" + s + "'")[0];
            row.BeginEdit();
            row.Delete();
            row.EndEdit();
            SqlCommand commandDelete = new SqlCommand();
            commandDelete.Connection = conn.con;
            commandDelete.CommandType = CommandType.Text;
            commandDelete.CommandText = @"Delete from PhieuHen Where MaPhieuHen=@MaPhieuHen";
            commandDelete.Parameters.Add("@MaPhieuHen", SqlDbType.NVarChar, 50, "MaPhieuHen");
            da.DeleteCommand = commandDelete;
            da.Update(dt3);
            MessageBox.Show("ban da xóa thanh cong", "chuc mung", MessageBoxButtons.OK);
            dt1.Clear();
            getdata1();
            binding1();
            dt2.Clear();
            getdata2();
            binding2();
        }

        private void button9_Click(object sender, EventArgs e)
        {
            if (textBox21.Text == "")
                MessageBox.Show("Ban Chua Nhap Ma Phieu Hen", "Loi Nhap", MessageBoxButtons.OK);
            else
                {
                    dt5.Clear();
                    SqlCommand command = new SqlCommand();
                    command.Connection = conn.con;
                    command.CommandType = CommandType.Text;
                    command.CommandText = @"Select * 
                                    from PhieuHen where MaPhieuHen like '%'+@DoiSo+'%'";
                    command.Parameters.Add("@DoiSo", SqlDbType.NVarChar, 50).Value = textBox21.Text;
                    da.SelectCommand = command;
                    da.Fill(dt5);
                    if (dt5.Rows.Count > 0)
                        dataGridView5.DataSource = dt5;
                    else
                    {
                        dt5.Clear();
                        MessageBox.Show("Tim Khong co", "Khong co thong tin", MessageBoxButtons.OK);
                    }
                }
        }

        private void button11_Click(object sender, EventArgs e)
        {
            if (textBox22.Text == "")
                MessageBox.Show("Chua Nhap Ten Nhan Vien");
            else
            {
                dt6.Clear();
                SqlCommand command = new SqlCommand();
                command.Connection = conn.con;
                command.CommandType = CommandType.Text;
                command.CommandText = @"Select *  
                                   from PhieuHen,NhanVien
                                  Where  PhieuHen.MaNV=NhanVien.MaNV and PhieuHen.TenNV like '%'+@TenNV+'%'";
                command.Parameters.Add("@TenNV", SqlDbType.NVarChar, 50).Value = textBox22.Text;
                da.SelectCommand = command;
                da.Fill(dt6);
                if (dt6.Rows.Count > 0)
                    dataGridView6.DataSource = dt6;
                else
                {
                    dt6.Clear();
                    MessageBox.Show("tim Khong co", "Khong co thong tin", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                   
                }
            }
        }

        private void button3_Click_1(object sender, EventArgs e)
        {
            dt4.Clear();
            getdata4();
            //binding4();
        }

        private void tabPage4_Click(object sender, EventArgs e)
        {

        }

        private void bt_them_Click_1(object sender, EventArgs e)
        {
            if (textBox1.Text == "" | textBox2.Text == "" | textBox3.Text == "" | textBox4.Text == "" | textBox5.Text == "")
                MessageBox.Show("Bạn Chưa Nhập Đủ Thông Tin", "Thông Báo", MessageBoxButtons.OK);
            else
            {
                if (check("Select MaPhieuHen from PhieuHen where MaPhieuHen ='%'+@DoiSo+'%'", textBox1.Text) == false)
                {
                    if (check("Select MaNV from NhanVien where MaNV ='%'+@DoiSo+'%'", textBox3.Text) == true)
                    {
                            DataRow row = dt1.NewRow();
                            row["Mã Phiếu Hẹn"] = textBox1.Text;
                            row["Tên Bệnh Nhân"] = textBox2.Text;
                            row["Mã Nhân Viên"] = textBox3.Text;
                            row["Thời Gian"] = textBox4.Text;
                            row["Phòng Kiểm Tra"] = textBox5.Text;
                            dt1.Rows.Add(row);
                            SqlCommand commandInsert = new SqlCommand();
                            commandInsert.Connection = conn.con;
                            commandInsert.CommandType = CommandType.Text;
                            commandInsert.CommandText = @"Insert PhieuHen (MaPhieuHen, TenBN, MaNV, ThoiGian, PhongKiemTra) Values 
                                                ( @MaPhieuHen, @TenBN,  @MaNV, @ThoiGian, @PhongKiemTra)";
                            commandInsert.Parameters.Add("@MaPhieuHen", SqlDbType.NVarChar, 50, "Mã Phiếu Hẹn");
                            commandInsert.Parameters.Add("@TenBN", SqlDbType.NVarChar, 50, "Tên Bệnh Nhân");
                            commandInsert.Parameters.Add("@MaNV", SqlDbType.NVarChar, 50, "Mã Nhân Viên");
                            commandInsert.Parameters.Add("@ThoiGian", SqlDbType.Date, 50, "Thời Gian");
                            commandInsert.Parameters.Add("@PhongKiemTra", SqlDbType.NVarChar, 50, "Phòng Kiểm Tra");
                            da.InsertCommand = commandInsert;
                            da.Update(dt1);
                            dt2.Clear();
                            getdata2();
                            binding2();
                            dt3.Clear();
                            getdata3();
                            binding3();
                            MessageBox.Show("Ban da them Thanh Cong", "chuc Mung", MessageBoxButtons.OK);
                    }
                    else MessageBox.Show("Ma Nhan Vien khong dung", "Loi Nhap MaNV", MessageBoxButtons.OK);
                }
                else
                {
                    MessageBox.Show("Ma bi trung", "chuc Mung", MessageBoxButtons.OK);
                }
            }
        }

        private void tabPage1_Click(object sender, EventArgs e)
        {

        }

    }
}
